package model.dao;

import java.io.InputStream;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.sql.DataSource;

import org.apache.commons.io.IOUtils;
import org.apache.log4j.Logger;
import org.springframework.core.io.FileSystemResource;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.UncategorizedSQLException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;

public class CommonsDao {

	private static Logger logger = Logger.getLogger(CommonsDao.class);

	private JdbcTemplate jdbcTemplate;

	public void setDataSource(DataSource dataSource) {
		jdbcTemplate = new JdbcTemplate(dataSource);
	}

	public String getParam(String key) {
		List<String> list = jdbcTemplate.queryForList("SELECT PARAM_VALUE FROM PARAMS WHERE PARAM_ID = ?",
				new Object[] { key }, String.class);
		String res = null;
		if (!list.isEmpty()) {
			res = list.get(0);
		}
		return res;
	}

	public void setParam(String key, String value) {
		String param = getParam(key);
		String sql = null;
		if (param == null || param.isEmpty()) {
			sql = "insert into PARAMS (PARAM_VALUE, PARAM_ID) values (?, ?)";
		} else {
			sql = "UPDATE PARAMS set PARAM_VALUE = ? where PARAM_ID = ?";
		}
		jdbcTemplate.update(sql, value, key);
	}

	public CommonsDao() {
		this.setDataSource(DataSourceFactory.getDataSource());
	}

	public void exectuteScript(String path, String separator) {
		logger.debug("Loading script: " + path);
		Resource resource = new FileSystemResource(path);
		runSqlFromScriptResource(resource, separator);
	}

	@Transactional(rollbackFor = Exception.class, isolation = Isolation.READ_UNCOMMITTED)
	private void runSqlFromScriptResource(Resource resource, String separator) {
		try {
			// The below is not recommended for DDL and it is not transactional
			InputStream is = resource.getInputStream();
			String[] sql = IOUtils.toString(is).split(separator);

			for (int i = 0; i < sql.length; i++) {
				String sqlstr = sql[i].trim();
				try {
					// limpio los dobles spaces
					sqlstr = sqlstr.replaceAll("(\\s)\\1", "$1");
					jdbcTemplate.update(sqlstr);
				} catch (UncategorizedSQLException e) {
					sqlstr = removeDoubleComma(sqlstr);
					sqlstr = limpiarComillas(sqlstr);
					jdbcTemplate.update(sqlstr);
				}
			}
		} catch (Exception e) {
			logger.error(e);
			throw new RuntimeException("Unable to run sql script against ", e);
		}
	}

	private static String removeDoubleComma(String text) {

		String expDoubleColonWithWords = "\\w\\s*,\\s*,\\s*\\w";
		String expDoubleColon = "\\s*,\\s*,\\s*";
		String endCommaPattern = "\\w\\s*,\\s*\\'";

		Pattern patern = Pattern.compile(expDoubleColonWithWords);
		Matcher m = patern.matcher(text);
		while (m.find()) {
			text = text.replaceAll(m.group(0), m.group(0).replaceAll(expDoubleColon, ", "));
		}
		Pattern patern2 = Pattern.compile(endCommaPattern);
		Matcher m2 = patern2.matcher(text);
		while (m2.find()) {
			text = text.replaceAll(m2.group(0), m2.group(0).replaceAll(",", ""));
		}

		return text;
	}

	private static String limpiarComillas(String string) {
		return string.replaceAll("[\\s*]'[\\s*^,]", " ");
	}

	public void excecuteQuery(String sql) {
		jdbcTemplate.execute(sql);
	}

	public Object excecuteReturnQuery(String sql) {
		return jdbcTemplate.queryForList(sql);
	}

	public List<Map<String, Object>> selectAllFromTable(String tableName) {
		String sql = "select * from " + tableName;
		return jdbcTemplate.queryForList(sql);
	}

	public void createTemporalTable(String name, int i) {
		deleteTemporalTable(name);
		String sql = "create table if not exists " + name + "(";
		for (int j = 0; j < i; j++) {
			sql += " a" + j + " varchar, ";
		}
		sql = sql.substring(0, sql.lastIndexOf(","));
		sql += ")";
		jdbcTemplate.execute(sql);
	}

	public void deleteTemporalTable(String name) {
		jdbcTemplate.execute("drop table if exists " + name);
	}

}